package com.community.sqlapp.service;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import lombok.RequiredArgsConstructor;
import okhttp3.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

/**
 * AI大模型生成sql语法实现类
 */
@Service
@RequiredArgsConstructor
public class QianwenToSqlService {

    @Value("${qianwen.api.url}")
    private String apiUrl;

    @Value("${qianwen.api.key}")
    private String apiKey;

    private final ObjectMapper objectMapper = new ObjectMapper();
    private final OkHttpClient client = new OkHttpClient.Builder()
        .connectTimeout(30, TimeUnit.SECONDS)
        .readTimeout(120, TimeUnit.SECONDS)
        .writeTimeout(30, TimeUnit.SECONDS)
        .build();
    /**
     * 通过千问API生成SQL
     * @param prompt 用户的SQL需求描述
     * @param tables 数据库表信息
     * @return 生成的SQL语句
     */
    public String generateSql(String prompt, Map<String, List<Map<String, Object>>> tables) {
        try {
            // 构建提示信息
            StringBuilder fullPrompt = new StringBuilder();
            fullPrompt.append("我需要生成一个SQL查询，数据库表结构如下：\n\n");

            // 添加表结构信息
            for (Map.Entry<String, List<Map<String, Object>>> entry : tables.entrySet()) {
                String tableName = entry.getKey();
                List<Map<String, Object>> columns = entry.getValue();

                fullPrompt.append("表名: ").append(tableName).append("\n");
                fullPrompt.append("列信息:\n");

                for (Map<String, Object> column : columns) {
                    fullPrompt.append("- ").append(column.get("Field"))
                            .append(", 类型: ").append(column.get("Type"))
                            .append(", 可空: ").append(column.get("Null"))
                            .append(", 键: ").append(column.get("Key"))
                            .append("\n");
                }
                fullPrompt.append("\n");
            }

            // 添加用户需求
            fullPrompt.append("基于以上表结构，请生成以下SQL查询:\n");
            fullPrompt.append(prompt).append("\n");
            fullPrompt.append("请只返回SQL语句，不要有任何解释。");

            // 构建流式请求
            ObjectNode requestBody = objectMapper.createObjectNode();
            //requestBody.put("model", "qwen-vl-max-latest");
            requestBody.put("model", "qwen-turbo");
            // 使用input.prompt而不是messages
            ObjectNode input = objectMapper.createObjectNode();
            input.put("prompt", fullPrompt.toString());
            requestBody.set("input", input);

            // 参数配置
            ObjectNode parameters = objectMapper.createObjectNode();
            parameters.put("result_format", "text");
            parameters.put("temperature", 0.1);
            parameters.put("top_p", 0.1);
            parameters.put("stream", true);
            requestBody.set("parameters", parameters);

/*            RequestBody body = RequestBody.create(
                    MediaType.parse("application/json"),
                    objectMapper.writeValueAsString(requestBody)
            );*/
            RequestBody body = RequestBody.create(
                    MediaType.parse("application/json"),
                    objectMapper.writeValueAsString(requestBody)
            );

            System.out.println("请求体: " + objectMapper.writeValueAsString(requestBody));
            Request request = new Request.Builder()
                    .url(apiUrl)
                    .addHeader("Authorization", "Bearer " + apiKey)
                    .addHeader("Content-Type", "application/json")
                    .post(body)
                    .build();
            //处理非流式响应
            StringBuilder responseBuilder = new StringBuilder();
            try (Response response = client.newCall(request).execute()) {
                if (!response.isSuccessful()) {
                    throw new IOException("API调用失败，状态码: " + response.code());
                }

                if (response.body() == null) {
                    throw new IOException("响应体为空");
                }

                String responseBody = response.body().string();
                System.out.println("响应体: " + responseBody); // 调试日志

                JsonNode node = objectMapper.readTree(responseBody);
                String text = node.path("output").path("text").asText();
                if (text != null && !text.isEmpty()) {
                    // 去除Markdown代码块标记
                    text = text.replace("```sql", "").replace("```", "").trim();
                    responseBuilder.append(text);
                } else {
                    throw new IOException("未收到有效响应内容");
                }
            }
            // 检查并返回结果
            String result = responseBuilder.toString().trim();
            if (result.isEmpty()) {
                throw new IOException("未收到有效响应");
            }

            // 去除可能的注释和前后空白
            //result = result.replaceAll("(?m)^--.*$", "").trim();
            // 确保返回的是有效的SQL语句
            if (!isValidSql(result)) {
                throw new IOException("API返回了无效的SQL语句: " + result);
            }
            return result;

        }
        catch (IOException e) {
            System.err.println("IO错误: " + e.getMessage());
            throw new RuntimeException("处理流式响应时发生IO错误", e);
        } catch (Exception e) {
            System.err.println("未知错误: " + e.getMessage());
            throw new RuntimeException("处理流式响应时发生未知错误", e);
        }
    }

    /**
     * @param sql 添加SQL有效性检查方法
     * @return
     */
    private boolean isValidSql(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return false;
        }

        // 简单的SQL格式检查
        String lowerSql = sql.toLowerCase();
        return lowerSql.startsWith("select") ||
                lowerSql.startsWith("insert") ||
                lowerSql.startsWith("update") ||
                lowerSql.startsWith("delete") ||
                lowerSql.startsWith("create") ||
                lowerSql.startsWith("alter") ||
                lowerSql.startsWith("drop");
    }
}
